﻿
using aier.dataaccess.model;
using Dapper;
using log4net;
using Microsoft.Xrm.Sdk;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class ReservationDal
    {

        public DapperHelper dapperHelper { get; set; }

        public ILog log { get; set; }

        /// <summary>
        /// 根据开始时间、结束时间、是否到院分页查询预约记录
        /// </summary>
        /// <param name="startdate"></param>
        /// <param name="enddate"></param>
        /// <param name="isHospital"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IEnumerable<ReservationView> QueryReservationByIstohospital(string startdate, string enddate, int isHospital, int pageIndex, int pageSize)
        {//需要跳过的记录数
            int skipIndex = (pageIndex - 1) * (pageSize - 1);
            startdate = ParseUTCMidNight(startdate).ToString();
            enddate = ParseUTCMidNight(enddate).ToString();
            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = $@"select r.new_mobile,r.new_reservationId,new_name,r.new_reservationdate,r.new_hospitalName,r.new_departmentName,r.new_istohospital,l.fullname as leadName 
                                    from new_reservation r
                                    left join lead l on r.new_lead = LeadId
                                    where new_reservationdate >= '{startdate}' and new_reservationdate <= '{enddate}' and new_istohospital = {isHospital}
                                    order by new_reservationdate asc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} ROWS ONLY";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                log.Info(string.Format("ReservationDal 对像中,QueryReservationByIstohospital 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ReservationView>(sqlTemplate.RawSql);
            });
        }


        public DateTime ParseUTCMidNight(string time) {
            DateTime dateTime = Convert.ToDateTime(time);
            return DateTime.Parse(dateTime.ToString("yyyy-MM-dd")).ToUniversalTime();
        }

        /// <summary>
        /// 根据开始时间、结束时间、是否到院查询预约记录总数
        /// </summary>
        /// <param name="startdate"></param>
        /// <param name="enddate"></param>
        /// <param name="isHospital"></param>
        /// <returns></returns>
        public int QueryReservationByIstohospitalCount(string startdate, string enddate, int isHospital)
        {//需要跳过的记录数


            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = $@"select count(1)
                                    from new_reservation 
                                    where new_reservationdate >= '{startdate}' and new_reservationdate <= '{enddate}'
                                    and new_istohospital = {isHospital}";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                log.Info(string.Format("ReservationDal 对像中,QueryReservationByIstohospitalCount 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.ExecuteScalar<int>(sqlTemplate.RawSql);
            });
        }

        public ReservationView FetchReservationById(Guid reservationid) {
            return dapperHelper.ExecueWithoutTransaction<ReservationView>(a =>
            {
                string sql = @"select r.new_remark,r.OwnerIdName,r.new_isoperate,r.new_hospitalizeddate,r.new_gender,r.new_source,r.new_mobile,r.new_reservationId,new_name,r.new_reservationdate,r.new_hospitalName,r.new_departmentName,r.new_istohospital,l.fullname as leadName
                                    from new_reservation r
                                    left join lead l on r.new_lead = LeadId
                                    /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_reservationId = @reservationid ", new { reservationid = reservationid });
                log.Info(string.Format("ReservationDal 对像中,FetchReservationById 方法 ,SQL：'{0}',Parameters：'{1}'", sqlTemplate.RawSql,sqlTemplate.Parameters));
                return a.Query<ReservationView>(sqlTemplate.RawSql, sqlTemplate.Parameters).First();
            });
        }

        /// <summary>
        /// 根据潜在顾客ID,筛查ID查询预约记录
        /// </summary>
        /// <param name="leadid"></param>
        /// <param name="scid"></param>
        /// <returns></returns>
        public IEnumerable<ReservationView> QueryReservationByLeadIdAndScid(Guid leadid, Guid scid)
        {

             return dapperHelper.ExecueWithoutTransaction(a =>
             {
                 string sql = $@"select * from new_reservation s  where s.new_lead='{leadid}' and s.new_screen ='{scid}' ";

                 SqlBuilder sqlBuilder = new SqlBuilder();
                 var sqlTemplate = sqlBuilder.AddTemplate(sql);
                 return a.Query<ReservationView>(sqlTemplate.RawSql);
             });
        }

        /// <summary>
        /// 根据潜在顾客ID查询预约记录
        /// 条件：潜在顾客ID
        /// ReservationServiceImpl/QueryReservationByLeadId
        /// </summary>
        /// <param name="leadId"></param>
        /// <returns></returns>
        public ReservationView QueryReservationByLeadId(Guid leadid, bool new_insurancetype = false)
        {
            ReservationView  reservationView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = string.Empty;
                if (new_insurancetype == false)
                {
                    sql = @"select top 1 * from AIER_RESERVATION_INFO_FOR_ACCOUNT s /**where**/ /**orderby**/";
                }else if (new_insurancetype == true) {
                    sql = @"select top 1 *,case r.new_insurancetype 
                                     when 9 then '城镇医保'
                                     when 10 then '小城镇医保'
                                     when 11 then '互助帮困'
                                     when 12 then '居民保险'
                                     when 13 then '中小学生医保'
                                     when 14 then '离休'
                                     when 15 then '伤残'
                                     when 16 then '新三险外来从业人员'
                                     when 1 then '省医保'
                                     when 17 then '精简回乡'
                                     when 2 then '城镇职工医保'
                                     when 3 then '城乡居民医保'
                                     when 4 then '新农合'
                                     when 6 then '铁路保险'
                                     when 7 then '商业保险'
                                     when 8 then '国际保险'
                                     when 5 then '其他'
                                     else '无数据'
                                     end as new_insurancetypeName 
                                     from AIER_RESERVATION_INFO_FOR_ACCOUNT s 
                                     left join new_reservation r on s.new_reservationid = r.new_reservationId /**where**/ /**orderby**/";
                }
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (leadid != Guid.Empty)
                {
                    sqlBuilder.Where(" s.LeadId=@LeadId ");
                    sqlParams.Add("@LeadId", leadid);
                }
                sqlBuilder.OrderBy("s.createdon desc");
                reservationView = a.QueryFirstOrDefault<ReservationView>(sqlTemplate.RawSql, sqlParams);
            });

            return reservationView;
        }


        /// <summary>
        /// 根据潜在顾客ID查询预约记录列表
        /// 条件：潜在顾客ID
        /// ReservationServiceImpl/QueryReservationByLeadId
        /// </summary>
        /// <param name="leadId"></param>
        /// <returns></returns>
        public IEnumerable<ReservationView> QueryReservationByLeadIdList(Guid leadid, bool new_insurancetype = false)
        {
            string sql = string.Empty;
            if (new_insurancetype == false)
            {
                sql = @"select * from AIER_RESERVATION_INFO_FOR_ACCOUNT s /**where**/ /**orderby**/";
            }
            else if (new_insurancetype == true)
            {
                sql = @"select  *,case r.new_insurancetype 
                                     when 9 then '城镇医保'
                                     when 10 then '小城镇医保'
                                     when 11 then '互助帮困'
                                     when 12 then '居民保险'
                                     when 13 then '中小学生医保'
                                     when 14 then '离休'
                                     when 15 then '伤残'
                                     when 16 then '新三险外来从业人员'
                                     when 1 then '省医保'
                                     when 17 then '精简回乡'
                                     when 2 then '城镇职工医保'
                                     when 3 then '城乡居民医保'
                                     when 4 then '新农合'
                                     when 6 then '铁路保险'
                                     when 7 then '商业保险'
                                     when 8 then '国际保险'
                                     when 5 then '其他'
                                     else ''
                                     end as new_insurancetypeName,
                                     
                                   case s.new_contnt
                                    when 7 then '近视手术'
                                    when 3 then '高度近视'
                                    when 11 then '医学验光配镜'
                                    when 2 then '儿童眼病'
                                    when 8 then '斜弱视'
                                    when 5 then '角膜塑形镜'
                                    when 1 then '白内障'
                                    when 9 then '眼底病'
                                    when 4 then '角膜及眼表'
                                    when 10 then '眼整形眼眶病'
                                    when 12 then '青光眼'
                                    when 6 then '泪道病'
                                    when 14 then '角膜移植'
                                    when 15 then '青光VIP特需门诊眼'
                                    when 13 then '其他'
                                    else ''
                                    end as new_contntname,
                                                                    
                                     r.new_departmentName
                                     from AIER_RESERVATION_INFO_FOR_ACCOUNT s 
                                     left join new_reservation r on s.new_reservationid = r.new_reservationId /**where**/ /**orderby**/";
            }

            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction(a =>
             {

                 var sqlTemplate = sqlBuilder.AddTemplate(sql);

                 if (leadid != Guid.Empty)
                 {
                     sqlBuilder.Where(" s.LeadId=@LeadId ");
                     sqlParams.Add("@LeadId", leadid);
                 }
                 sqlBuilder.OrderBy("s.createdon desc");
                 return a.Query<ReservationView>(sqlTemplate.RawSql, sqlParams);
             });

        }

        /// <summary>
        /// 查询同一医院,同一科室,同一潜客,同一日期只能有一条预约记录
        /// </summary>
        /// <param name="leadid">潜客ID</param>
        /// <param name="reservationDate">预约日期</param>
        /// <param name="hospitalId">医院ID</param>
        /// <param name="departmentId">科室ID</param>
        /// <returns></returns>
        public int QueryReservationBy(Guid leadid,DateTime startDate,DateTime endDate,Guid hospitalId,Guid departmentId)
        {
            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = $@"select count(1) from new_reservation r where  r.new_lead = '{leadid}'  and r.new_hospital = '{hospitalId}' and r.new_department = '{departmentId}' and  r.new_reservationdate >= '{startDate}' and r.new_reservationdate<='{endDate}' ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.ExecuteScalar<int>(sqlTemplate.RawSql);
            });
        }

        public Guid QueryReservationId(Guid leadid, DateTime startDate, DateTime endDate, Guid hospitalId, Guid departmentId)
        {
            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = $@"select new_reservationid from new_reservation r where  r.new_lead = '{leadid}'  and r.new_hospital = '{hospitalId}' and r.new_department = '{departmentId}' and  r.new_reservationdate >= '{startDate}' and r.new_reservationdate<='{endDate}' ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.ExecuteScalar<Guid>(sqlTemplate.RawSql);
            });
        }

        public Guid CheckExistsReservation(Guid leadid, DateTime startDate, DateTime endDate, Guid hospitalId, Guid departmentId,int item)
        {
            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = $@"select new_reservationid from new_reservation r where  r.new_lead = '{leadid}'  and r.new_hospital = '{hospitalId}' and r.new_department = '{departmentId}' and  r.new_reservationdate >= '{startDate}' and r.new_reservationdate<='{endDate}' and r.new_contnt='{item}' ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.ExecuteScalar<Guid>(sqlTemplate.RawSql);
            });
        }
    }
}
